#! /usr/bin/env python
# _*_ coding:UTF-8 _*_
"""
@author:zhh
@Software:PyCharm
@blogs:https://blog.csdn.net/smilehaoh
@time:2020/4/1

"""
from common.connect_db import MyDB

my_db = MyDB()


class Investment:

    @staticmethod
    def get_old_investorder():
        """
        查询老系统总的投资订单数
        :return:
        """
        sql = "select count(*) as orders  from ims_ewei_shop_investment_order where deleted=0 and status=1 and mid in (select id from ims_ewei_shop_member where (mobile regexp '[^0-9.]')=0) "
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_investmember():
        """
        查询老系统总的投资会员数（一个会员投资多个项目算多个会员）
        :return:
        """
        sql = "select count(*) as members from ims_ewei_invest_member"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_investtotal():
        """
        查询老系统总的投资股数
        :return:
        """
        sql = "select sum(total) as total from ims_ewei_invest_member"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_investcredits():
        """
        查询老系统投资总的积分
        :return:
        """
        sql = "select sum(nowcredit1) as nowcredit1 from ims_ewei_invest_member where mid in (select id from ims_ewei_shop_member where (mobile regexp '[^0-9.]')=0)"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_investcoin():
        """
        查询老系统总的微币数
        :return:
        """
        sql = "select sum(nowcredit2) as totalcoin from ims_ewei_invest_member where mid in (select id from ims_ewei_shop_member where (mobile regexp '[^0-9.]')=0)"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_investqtorder(sta):
        """
        查询老系统投资其他订单
        订单状态 -1已取消 0待付款 1已付款  2退股申请中 3待退款 4退款中 5已退款
        :return:
        """
        sql = "select count(*) as qtorder from ims_ewei_shop_investment_order " \
              "where status='%s'" % sta
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_investrefund():
        """
        查询老系统投资其他订单
        订单状态 -1已取消 0待付款 1已付款  2退股申请中 3待退款 4退款中 5已退款
        :return:
        """
        sql = "select count(*) as qtorder from ims_ewei_shop_investment_order " \
              "where status in (2,3,4)"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_investorders1():
        """
        查询老系统投资其他订单
        订单状态 -1已取消 0待付款 1已付款  2退股申请中 3待退款 4退款中 5已退款
        :return:
        """
        sql = "select ordersn as qtorder from ims_ewei_shop_investment_order where status=2  "
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_investqtorderss():
        """
        查询老系统投资其他订单
        订单状态 -1已取消 0待付款 1已付款  2退股申请中 3待退款 4退款中 5已退款
        :return:
        """
        sql = "select count(*) as qtorder from ims_ewei_shop_investment_order " \
              "where status in (1,2)"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_investqtorders():
        """
        查询老系统投资其他订单
        订单状态 -1已取消 0待付款 1已付款  2退股申请中 3待退款 4退款中 5已退款
        :return:
        """
        sql = "select ordersn from ims_ewei_shop_investment_order where status in (3,4,5)"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_investqtorder1():
        """
        查询老系统投资其他订单
        订单状态 -1已取消 0待付款 1已付款  2退股申请中 3待退款 4退款中 5已退款
        :return:
        """
        sql = "select count(*) as qtorder from ims_ewei_shop_investment_order " \
              "where status in (1,2,3,4)"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_investorder():
        """
        查询新系统会员的总投资订单
        :return:
        """
        sql = "select count(*) as investorder from life_investment_project_orders  "
        result = my_db.execute_select(select_db='new', db_name="third", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_investorder():
        """
        查询新系统会员的总投资订单
        :return:
        """
        sql = "select count(*) as investorder from life_investment_project_orders  "
        result = my_db.execute_select(select_db='new', db_name="third", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_investmember():
        """
        查询新系统总的投资会员数
        :return:
        """
        sql = "select count(distinct(member_id)) as members from life_investment_accounts"
        result = my_db.execute_select(select_db='new', db_name="third", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_everyinvestmember():
        """
        查询每一个投资项目的投资会员、投资订单数，后面有时间在写
        :return:
        """
        pass

    @staticmethod
    def get_new_investtotal():
        """
        查询新系统总的投资股数
        :return:
        """
        sql = "select sum(shares) as totalinvest from life_investment_project_orders"
        result = my_db.execute_select(select_db='new', db_name="third", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_investcredits():
        """
        查询新系统总的投资积分
        :return:
        """
        sql = "select sum(now_credits) as totalinvestcredits from common_coin_credits where account_type=2"
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_investcoin():
        """
        查询新系统总的投资微币
        :return:
        """
        sql = "select sum(now_coin) as totalinvestcoin from common_coin_coin where account_type=2"
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_investqtorder(sta):
        """
        查询新系统总的投资正向订单
        :return:
        """
        sql = "select count(*) as totalorder from life_investment_project_orders" \
              " where status='%s'" % sta
        result = my_db.execute_select(select_db='new', db_name="third", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_investqtorder10():
        """
        查询新系统总的投资正向订单
        :return:
        """
        sql = "select ordersn as totalorder from life_investment_project_orders where status=-2 "
        result = my_db.execute_select(select_db='new', db_name="third", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_investqtorder1():
        """
        查询新系统总的投资正向订单
        :return:
        """
        sql = "select ordersn  from life_investment_project_orders where status=-2"
        result = my_db.execute_select(select_db='new', db_name="third", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_investnxorder(sta):
        """
        查询新系统总的投资逆向订单
        :return:
        """
        sql = "select count(*) as totalorder from life_investment_refunds" \
              " where status='%s'" % sta
        result = my_db.execute_select(select_db='new', db_name="third", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")


if __name__ == '__main__':
    invest = Investment()
    oldytkinvestorder = invest.get_old_investorders1()
    newtkzinvestorder = invest.get_new_investqtorder10()
    lista = []
    listb = []
    for item in oldytkinvestorder:
        a = item[0]
        lista.append(a)
    for item in newtkzinvestorder:
        b = item[0]
        listb.append(b)
    c = set(lista).difference(set(listb))
    # print(lista)
    #  print(listb)

    print(c)
